exec aspnet_Roles_CreateRole @ApplicationName = '/', @RoleName = 'user'
go
CREATE TABLE [dbo].[Country](
	[CountryId] [int] IDENTITY(1,1) NOT NULL,
	[CountryName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[CountryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PaymentType](
	[PaymentTypeId] [int] NOT NULL,
	[PaymentTypeName] [varchar](50) NOT NULL,
	[PaymentTypeSumma] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_PaymentType] PRIMARY KEY CLUSTERED 
(
	[PaymentTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PaymentType] ([PaymentTypeId], [PaymentTypeName], [PaymentTypeSumma]) VALUES (1, N'Trial - Limited Time Only!', CAST(0.00 AS Decimal(18, 2)))
INSERT [dbo].[PaymentType] ([PaymentTypeId], [PaymentTypeName], [PaymentTypeSumma]) VALUES (2, N'monthly', CAST(29.95 AS Decimal(18, 2)))
INSERT [dbo].[PaymentType] ([PaymentTypeId], [PaymentTypeName], [PaymentTypeSumma]) VALUES (3, N'3-months', CAST(59.95 AS Decimal(18, 2)))
INSERT [dbo].[PaymentType] ([PaymentTypeId], [PaymentTypeName], [PaymentTypeSumma]) VALUES (4, N'12 month', CAST(89.95 AS Decimal(18, 2)))
GO

CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[UserName] [varchar](256) NOT NULL,
	[FirstName] [varchar](100) NOT NULL,
	[LastName] [varchar](100) NOT NULL,
	[Email] [varchar](256) NOT NULL,
	[PaymentTypeId] [int] NOT NULL,
	[CountryId] [int] NULL,
	[Zip] [varchar](50) NULL,
	[IsActive] [bit] NOT NULL,
	[IsAdmin] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_aspnet_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_aspnet_Users]
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([CountryId])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Country]
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_PaymentType] FOREIGN KEY([PaymentTypeId])
REFERENCES [dbo].[PaymentType] ([PaymentTypeId])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_PaymentType]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_IsAdmin]  DEFAULT ((0)) FOR [IsAdmin]
GO
create PROCEDURE spUser_Add
(
	@UserName nvarchar(256),
	@FirstName varchar(100),
	@LastName varchar(100),
	@Email nvarchar(256),
	@PaymentTypeId int,
	@CountryId int = null,
	@Zip varchar(50) = null,
	
	@Password nvarchar(128),
	@PasswordSalt nvarchar(128)
)
AS
BEGIN
	set nocount on
	set xact_abort on
	
	declare @userGuid uniqueidentifier = NEWID()
	declare @appId uniqueidentifier
	declare @roleUserId uniqueidentifier
	
	select @appId = ApplicationId
	from aspnet_Applications
	
	select @roleUserId = RoleId
	from aspnet_Roles
	where RoleName = 'user'
	
    begin transaction
		insert into aspnet_Users
		(ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate)
		values (@appId, @userGuid, @UserName, LOWER(@UserName), NULL, 0, GETDATE())
		
		insert into aspnet_Membership
		(ApplicationId, UserId, Password, PasswordFormat, PasswordSalt, MobilePIN, Email, LoweredEmail, 
			PasswordQuestion, PasswordAnswer, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, 
			LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Comment)
		values (@appId, @userGuid, @Password, 0, @PasswordSalt, NULL, @Email, LOWER(@Email), 
			NULL, NULL, 1, 0, GETDATE(), GETDATE(), GETDATE(), 
			0, 0, 0, 0, 0, NULL)
			
		insert into aspnet_UsersInRoles
		(UserId, RoleId)
		values (@userGuid, @roleUserId)
			
		INSERT INTO [User]
		(UserId, UserName, FirstName, LastName, Email, PaymentTypeId, CountryId, Zip, IsAdmin, IsActive)
		VALUES (@userGuid, @UserName, @FirstName, @LastName, @Email, @PaymentTypeId, @CountryId, @Zip, 0, 1)
	COMMIT TRANSACTION
END
go




